/*==================================================
Project:       Targeting Social Programs
Authors:       Diether W. Beuermann
               Bridget Hoffmann        
               Marco Stampini 
               David L. Vargas
               Diego Vera-Cossio
----------------------------------------------------
Creation Date:    May 2023
Modification Date:   
Do-file version:    01
References:          
Output:             
==================================================*/

/*==================================================
			0: Program set up
==================================================*/
*Written on STATA 17
drop _all

** source dir
cd "${dir4r}" // Table dir


* ssc install winsor2

/*==================================================
			Make graphs 
==================================================*/

** load bootsrap estimates
use "${dir3r}/03_auxiliar/Boostrap_estimates.dta" , clear

 
cap erase "Table_main.xls"
tokenize "`c(ALPHA)'" // number token to alphabet

putexcel set "Table_main.xls", replace
putexcel A1  = "", hcenter bold border(top, thick) 
putexcel B1  = "Baseline", hcenter bold border(top, thick)
putexcel C1:D1  = "Benchmark PMT", hcenter bold border(top, thick) merge
putexcel G1:H1  = "Updated PMT - Assets", hcenter bold border(top, thick) merge
putexcel E1:F1  = "Expanded Coverage", hcenter bold border(top, thick) merge
putexcel I1:J1  = "Dynamic", hcenter bold border(top, thick) merge

putexcel A2  = "", hcenter bold border(bottom, thick) 
putexcel B2 = "2019" , hcenter bold border(bottom, thick)
putexcel C2 = "2020" , hcenter bold border(bottom, thick)
putexcel D2 = "2021" , hcenter bold border(bottom, thick)

putexcel E2 = "2020" , hcenter bold border(bottom, thick)
putexcel F2 = "2021" , hcenter bold border(bottom, thick)
putexcel G2 = "2020" , hcenter bold border(bottom, thick)
putexcel H2 = "2021" , hcenter bold border(bottom, thick)
putexcel I2 = "2020" , hcenter bold border(bottom, thick)
putexcel J2 = "2021" , hcenter bold border(bottom, thick)

putexcel A4 = "Coverage (%)" , left
putexcel A6 = "Per-household monthly transfer USD PPP" ,  left
putexcel A8 = "Inclusion error (%)" , left
putexcel A10 = "Exclusion error (%)" , left
putexcel A12 = " % of households with 0 income after transfers" ,  left
putexcel A14 = " % of households in extreme poverty after transfers" ,  left
putexcel A16 = "Social Welfare" ,  left
putexcel A17 = "\rho=1.5" ,  left 
putexcel A19 = "\rho=3" ,  left
putexcel A21 = "\rho=4.5" ,  left


**** Baseline PMT
foreach t in 2019 2020 2021 {
if `t'==2019 {
	local cell B
}
if `t'==2020 {
	local cell C
}

else if `t'==2021 {
	local cell D
}

local j=3
foreach v in  covered hh_benefits inclusion_err  exclusion_err zero epov {
local j=`j'+1
sum `v' if year==`t' & type_m ==  "Baseline"
local content: di %3.1f r(mean)

putexcel `cell'`j' = "`content'" , hcenter
sum  `v'_lci if year==`t' & type_m ==  "Baseline"
local lci: di %3.1f r(mean)
sum  `v'_uci if year==`t' & type_m ==  "Baseline"
local uci: di %3.1f r(mean)
local j=`j'+1
putexcel `cell'`j' = "[`lci',`uci']" , hcenter
}
local j=16 
foreach v in crra_l  crra crra_u {
local j=`j'+1
sum `v' if year==`t' & type_m ==  "Baseline"
local content: di %9.4g r(mean)
putexcel `cell'`j' = "`content'" , hcenter
sum  `v'_lci if year==`t' & type_m ==  "Baseline"
local lci: di %9.4g r(mean)
sum  `v'_uci if year==`t' & type_m ==  "Baseline"
local uci: di %9.4g r(mean)
local j=`j'+1
putexcel `cell'`j' = "[`lci',`uci']" , hcenter
}
}

**** Updated PMT
foreach t in 2020 2021 {

if `t'==2020 {
	local cell G
}

else if `t'==2021 {
	local cell H
}

local j=3
foreach v in  covered hh_benefits inclusion_err  exclusion_err zero epov {
local j=`j'+1
sum `v' if year==`t' & type_m ==  "Updated"
local content: di %3.1f r(mean)

putexcel `cell'`j' = "`content'" , hcenter
sum  `v'_lci if year==`t' & type_m ==  "Updated"
local lci: di %3.1f r(mean)
sum  `v'_uci if year==`t' & type_m ==  "Updated"
local uci: di %3.1f r(mean)
local j=`j'+1
putexcel `cell'`j' = "[`lci',`uci']" , hcenter
}

local j=16 
foreach v in crra_l  crra crra_u {
local j=`j'+1
sum `v' if year==`t' & type_m ==  "Updated"
local content: di %9.4g r(mean)
putexcel `cell'`j' = "`content'" , hcenter
sum  `v'_lci if year==`t' & type_m ==  "Updated"
local lci: di %9.4g r(mean)
sum  `v'_uci if year==`t' & type_m ==  "Updated"
local uci: di %9.4g r(mean)
local j=`j'+1
putexcel `cell'`j' = "[`lci',`uci']" , hcenter
}
}

**** Expanded
foreach t in  2020 2021 {

if `t'==2020 {
	local cell E
}

else if `t'==2021 {
	local cell F
}

local j=3
foreach v in  covered hh_benefits inclusion_err  exclusion_err zero epov {
local j=`j'+1
sum `v' if year==`t' & type_m ==  "Expanded"
local content: di %3.1f r(mean)

putexcel `cell'`j' = "`content'" , hcenter
sum  `v'_lci if year==`t' & type_m ==  "Expanded"
local lci: di %3.1f r(mean)
sum  `v'_uci if year==`t' & type_m ==  "Expanded"
local uci: di %3.1f r(mean)
local j=`j'+1
putexcel `cell'`j' = "[`lci',`uci']" , hcenter
}

local j=16 
foreach v in crra_l  crra crra_u {
local j=`j'+1
sum `v' if year==`t' & type_m ==  "Expanded"
local content: di %9.4g r(mean)
putexcel `cell'`j' = "`content'" , hcenter
sum  `v'_lci if year==`t' & type_m ==  "Expanded"
local lci: di %9.4g r(mean)
sum  `v'_uci if year==`t' & type_m ==  "Expanded"
local uci: di %9.4g r(mean)
local j=`j'+1
putexcel `cell'`j' = "[`lci',`uci']" , hcenter
}
}

**** Dynamic

foreach t in  2020 2021 {

if `t'==2020 {
	local cell I
}

else if `t'==2021 {
	local cell J
}

local j=3
foreach v in  covered hh_benefits inclusion_err  exclusion_err zero epov {
local j=`j'+1
sum `v' if year==`t' & type_m ==  "OLS" & model_n ==  6
local content: di %3.1f r(mean)

putexcel `cell'`j' = "`content'" , hcenter
sum  `v'_lci if year==`t' & type_m ==  "OLS" & model_n ==  6
local lci: di %3.1f r(mean)
sum  `v'_uci if year==`t' & type_m ==  "OLS" & model_n ==  6
local uci: di %3.1f r(mean)
local j=`j'+1
putexcel `cell'`j' = "[`lci',`uci']" , hcenter
}

local j=16 
foreach v in crra_l  crra crra_u {
local j=`j'+1
sum `v' if year==`t' & type_m ==  "OLS" & model_n ==  6
local content: di %9.4g r(mean)
putexcel `cell'`j' = "`content'" , hcenter
sum  `v'_lci if year==`t' & type_m ==  "OLS" & model_n ==  6
local lci: di %9.4g r(mean)
sum  `v'_uci if year==`t' & type_m ==  "OLS" & model_n ==  6
local uci: di %9.4g r(mean)
local j=`j'+1
putexcel `cell'`j' = "[`lci',`uci']" , hcenter
}
}

putexcel A23:J23 = "" , hcenter bold border(bottom, thick) merge
